CREATE TABLE  Scott.tblCountries (
     CountryID   int  NOT NULL,
     Name   nvarchar2 (50)  NOT NULL,
     Capital   nvarchar2(50),
     Currency   nvarchar2(20),
     Languaje   nvarchar2(50),
     ContinendID   int  NOT NULL,
    PRIMARY KEY (CountryID)
) ;

INSERT INTO Scott.tblCountries  VALUES (1,'Japan','Tokyo','Yen','Japanese',3);
INSERT INTO Scott.tblCountries  VALUES (2,'China','Beijing','Yuan','Chinese',3);
INSERT INTO Scott.tblCountries  VALUES (3,'Germany','Berlin','Euro','German',2);
INSERT INTO Scott.tblCountries  VALUES (4,'Italy','Rome','Euro','Italian',2);
INSERT INTO Scott.tblCountries  VALUES (5,'Argentina','Buenos Aires','Peso','Spanish',1);
INSERT INTO Scott.tblCountries  VALUES (6,'Australia','Sidney','Aud','English',5);
INSERT INTO Scott.tblCountries  VALUES (7,'Brazil','Brasilia','Real','Portuguese',1);
INSERT INTO Scott.tblCountries  VALUES (8,'France','Paris','Euro','French',2);
INSERT INTO Scott.tblCountries  VALUES (9,'Russia','Moscow','RUB','Russian',3);
INSERT INTO Scott.tblCountries  VALUES (10,'Egypt','Cairo','Libra','Arabic',4);
INSERT INTO Scott.tblCountries  VALUES (11,'England','London','Euro','English',2);
INSERT INTO Scott.tblCountries  VALUES (12,'Spain','Madrid','Euro','Spanish',2);
INSERT INTO Scott.tblCountries  VALUES (13,'United States','Washington DC','Dolar','English',1);

CREATE TABLE  Scott.tblCities (
     CityID   int  NOT NULL,
     Name   nvarchar2 (50)  NOT NULL,
     CountryID   int  NOT NULL,
     Population   int  NOT NULL,
     Area   int  NOT NULL,
     Density   int  NOT NULL,
    PRIMARY KEY (CityID) 
) ;

INSERT INTO Scott.tblCities values(1,'Los Angeles',13,3833995,1290,3156);
INSERT INTO Scott.tblCities values(2,'Ashland, KY',13,833995,1290,3156);
INSERT INTO Scott.tblCities values(3,'Seattle',13,3833995,1290,3156);
INSERT INTO Scott.tblCities values(4,'San Antonio',13,3833995,1290,3156);
INSERT INTO Scott.tblCities values(5,'Dallas',13,24326974,693622,3412);
INSERT INTO Scott.tblCities values(6,'Tokyo',1,12790000,2187,5847);
INSERT INTO Scott.tblCities values(7,'Kyoto',1,2790000,2187,5847);
INSERT INTO Scott.tblCities values(8,'Beijing',2,17500000,16801,1044);
INSERT INTO Scott.tblCities values(9,'Shangai',2,5500000,16801,1044);
INSERT INTO Scott.tblCities values(10,'Berlin',3,3429870,891,3845);
INSERT INTO Scott.tblCities values(11,'Frankfurt',3,3429870,891,3845);
INSERT INTO Scott.tblCities values(12,'Munich',3,3429870,891,3845);
INSERT INTO Scott.tblCities values(13,'Wurzburgo',3,135212,87,3845);
INSERT INTO Scott.tblCities values(14,'Rome',4,273638,1285,2115);
INSERT INTO Scott.tblCities values(15,'Milan',4,273638,1285,2115);
INSERT INTO Scott.tblCities values(16,'Napoles',4,273638,1285,2115);
INSERT INTO Scott.tblCities values(17,'Firenze',4,962447,117,8263);
INSERT INTO Scott.tblCities values(18,'Ottawa',22,159704,2778,27812);
INSERT INTO Scott.tblCities values(19,'Toronto',22,8159704,2778,27812);
INSERT INTO Scott.tblCities values(20,'Vancouver',22,859704,2778,27812);
INSERT INTO Scott.tblCities values(21,'Sidney',6,4336374,12406,2058);
INSERT INTO Scott.tblCities values(23,'Buenos Aires',5,2995805,202,14825);
INSERT INTO Scott.tblCities values(24,'Cordoba',5,1309536,576,2273);
INSERT INTO Scott.tblCities values(25,'Bahia Blanca',5,1309536,576,2273);
INSERT INTO Scott.tblCities values(26,'Rosario',5,1309536,576,2273);
INSERT INTO Scott.tblCities values(27,'Brasilia',7,2606885,5802,4232);
INSERT INTO Scott.tblCities values(28,'Sao Paulo',7,2606885,5802,4232);
INSERT INTO Scott.tblCities values(29,'Rio de Janeiro',7,2606885,5802,4232);
INSERT INTO Scott.tblCities values(30,'Bahia',7,2606885,5802,4232);
INSERT INTO Scott.tblCities values(31,'Paris',8,2193030,105,20886);
INSERT INTO Scott.tblCities values(32,'Marsella',8,2193030,105,20886);
INSERT INTO Scott.tblCities values(33,'Moscow',9,10562099,1081,9722);
INSERT INTO Scott.tblCities values(34,'St Petersburg',9,10562099,1081,9722);
INSERT INTO Scott.tblCities values(35,'Cairo',10,17734334,1424501,35047);
INSERT INTO Scott.tblCities values(36,'London',11,755690,1706,4000);
INSERT INTO Scott.tblCities values(37,'Manchester',11,755690,1706,4000);
INSERT INTO Scott.tblCities values(38,'Liverpool',11,755690,1706,4000);
INSERT INTO Scott.tblCities values(39,'Madrid',12,3255944,605,5374);
INSERT INTO Scott.tblCities values(40,'Zaragoza',12,1255944,605,5374);
INSERT INTO Scott.tblCities values(41,'Barcelona',12,4255944,605,537);

CREATE OR REPLACE PACKAGE SCOTT.pcGetCitiesList AS
        TYPE type_cursor IS REF CURSOR ; 
        Procedure spGetCitiesList(Country IN Decimal,t0_cursor IN OUT type_cursor); 
END pcGetCitiesList;

CREATE OR REPLACE PACKAGE BODY SCOTT.pcGetCitiesList
AS
    Procedure spGetCitiesList (Country IN Decimal, t0_cursor IN OUT type_cursor) 
    IS 
        matrix_cursor type_cursor;
        BEGIN 
            OPEN matrix_cursor FOR 
                SELECT * 
                    FROM tblCities
                        WHERE CountryID = Country;
            t0_cursor := matrix_cursor;
        END spGetCitiesList; 
END pcGetCitiesList;


CREATE OR REPLACE PACKAGE SCOTT.pcGetDrownDownPickerData AS
    TYPE type_cursor IS REF CURSOR;
    PROCEDURE spGetDrownDownPickerData(t0_cursor IN OUT type_cursor,t1_cursor IN OUT type_cursor,t2_cursor IN OUT type_cursor);
END pcGetDrownDownPickerData;
/

CREATE OR REPLACE PACKAGE BODY SCOTT.pcGetDrownDownPickerData AS
    PROCEDURE spGetDrownDownPickerData(t0_cursor IN OUT type_cursor,t1_cursor IN OUT type_cursor,t2_cursor IN OUT type_cursor)
    IS
       tMeta type_cursor;
       tPicker0 type_cursor;
       tPicker1 type_cursor;
        BEGIN
            /* Table of contents */
           OPEN tMeta FOR
                SELECT  1 PickerID, 'Continent :' Title, '{SQLServer:"Int",Oracle:"Int"}' Type, 'ContinentID' Name, 0 ParentID, '' Attrib FROM DUAL
                UNION ALL
                SELECT         2,       'Country :',               '{SQLServer:"Int",Oracle:"Int"}',        'CountryID',                1,          ''      FROM DUAL;
           t0_cursor := tMeta;
            /* Table of pickers data*/
           OPEN tPicker0 FOR
                /* Countries */
                SELECT null ID, cast( 'All Continents' AS nvarchar2(50) ) Title, 1 IsDefaultValue, null ParentValue,  cast( '' AS nvarchar2(50) ) SortOrder FROM DUAL
                UNION ALL
                SELECT * FROM (SELECT CountryID ID,  Name Title, 0 IsDefaultValue, 0 ParentValue,Name SortOrder FROM SCOTT.TBLCOUNTRIES
                    ORDER BY SortOrder);
           t1_cursor := tPicker0;
           OPEN tPicker1 FOR
                /* Cities */
                SELECT null ID, cast( 'All Cities' AS nvarchar2(50) ) Title, 0 IsDefaultValue, null ParentValue,  cast( '' AS nvarchar2(50) ) SortOrder FROM DUAL
                UNION ALL
                SELECT * FROM (SELECT CityID ID,  Name Title, 0 IsDefaultValue, CountryID ParentValue,Name SortOrder FROM SCOTT.TBLCITIES
                    ORDER BY SortOrder);
            t2_cursor := tPicker1;
        END spGetDrownDownPickerData;
END pcGetDrownDownPickerData;
/
